﻿using Kanban.Dapper.Dal;
using Kanban.IDal;
using Kanban.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Kanban.Dal
{
    public class BoardDal : BaseDal<Board>, IBoardDal
    {
        public Task<object> GetNum()
        {
            string dateTime;
            if (DateTime.Now.Hour > 19 && DateTime.Now.Minute > 40)
            {
                dateTime = DateTime.Today.AddDays(-1).ToString("yyyy-MM-dd") + " 19:40:00";
            }
            else
            {
                dateTime = DateTime.Today.ToString("yyyy-MM-dd") + " 07:40:00";
            }
            var param = new
            {
                dateTime = dateTime,
            };

            return _db.GetAsync<object>("Get_看板数据", param, commandType: System.Data.CommandType.StoredProcedure);
        }

        public Task<IEnumerable<object>> GetRacks()
        {
            string sql = @"";
            return _WCSDB.ListAsync<object>(sql);
        }

        public Task<Tuple<IList<object>, int>> GetInPageData(string PKGID, string Location, string StoreCode, string SHELFTNO, string PN, string startTime, string endTime, int pageSize, int pageIndex,bool Qty = false)
        {
            string whereSql = "Memo = '正常' ";
            if (!string.IsNullOrEmpty(PKGID))
            {
                whereSql += $" AND SerialNo LIKE '%{PKGID}%'";
            }
            if (!string.IsNullOrEmpty(Location))
            {
                whereSql += $" AND Location LIKE '%{Location}%'";
            }
            if (!string.IsNullOrEmpty(StoreCode))
            {
                whereSql += $" AND 仓码 LIKE '%{StoreCode}%'";
            }
            if (!string.IsNullOrEmpty(PN))
            {
                whereSql += $" AND CPN LIKE '%{PN}%'";
            }
            if (!string.IsNullOrEmpty(startTime))
            {
                whereSql += $" AND CreateTime Between '{startTime}' and '{endTime}'";
            }
            if (Qty)
            {
                whereSql += $" AND Qty > 0";
            }
            return GetPageAsync("InWarehouse4WMS with(NOLOCK)", "*", whereSql, "Id DESC", pageSize, pageIndex);
        }

        public Task<Tuple<IList<object>, int>> GetOutPageData(string TaskNo, string PKGID, string Location, string StoreCode, string SHELFTNO, string PN, string startTime, string endTime, int pageSize, int pageIndex)
        {
            string whereSql = "1 = 1";
            if (!string.IsNullOrEmpty(TaskNo))
            {
                whereSql += $" AND TASKNO LIKE '%{TaskNo}%'";
            }
            if (!string.IsNullOrEmpty(PKGID))
            {
                whereSql += $" AND PKGID LIKE '%{PKGID}%'";
            }
            if (!string.IsNullOrEmpty(Location))
            {
                whereSql += $" AND LOCATION LIKE '%{Location}%'";
            }
            if (!string.IsNullOrEmpty(StoreCode))
            {
                whereSql += $" AND STORECODE LIKE '%{StoreCode}%'";
            }
            if (!string.IsNullOrEmpty(PN))
            {
                whereSql += $" AND PARTNO LIKE '%{PN}%'";
            }
            if (!string.IsNullOrEmpty(startTime))
            {
                whereSql += $" AND CreateDt Between '{startTime}' and '{endTime}'";
            }
            string table = "API_TASK_FINISH with(NOLOCK)";
            return GetPageAsync(table, "*", whereSql, "CreateDt DESC", pageSize, pageIndex);
        }

        public Task<Tuple<IList<object>, int>> GetWarehousePageData(string PKGID, string Location, string StoreCode, string SHELFTNO, string PN, int pageSize, int pageIndex)
        {
            string whereSql = "SerialNo is not null";
            if (!string.IsNullOrEmpty(PKGID))
            {
                whereSql += $" AND SerialNo LIKE '%{PKGID}%'";
            }
            if (!string.IsNullOrEmpty(Location))
            {
                whereSql += $" AND LOCATION LIKE '%{Location}%'";
            }
            if (!string.IsNullOrEmpty(StoreCode))
            {
                whereSql += $" AND 仓码 LIKE '%{StoreCode}%'";
            }
            if (!string.IsNullOrEmpty(PN))
            {
                whereSql += $" AND CustomerPartNo LIKE '%{PN}%'";
            }
            return GetPageAsync("Warehouse with(NOLOCK)", "*", whereSql, "Id DESC", pageSize, pageIndex);
        }

        public Task<Tuple<IList<object>, int>> GetTaskCancelPageData(string search, int pageSize, int pageIndex)
        {
            string whereSql = "1 = 1";
            if (!string.IsNullOrEmpty(search))
            {
                whereSql += $"AND (Name LIKE '%{search}%' OR ManagerName LIKE '%{search}%')";
            }
            return GetPageAsync("ProjectProgress", "*", whereSql, "Id DESC", pageSize, pageIndex);
        }

        public Task<Tuple<IList<object>, int>> GetTaskPageData(string TaskNo, string PKGID, string Location, string StoreCode, string SHELFTNO, string PN, int pageSize, int pageIndex)
        {
            string whereSql = "1 = 1";
            if (!string.IsNullOrEmpty(TaskNo))
            {
                whereSql += $" AND TASKNO LIKE '%{TaskNo}%'";
            }
            if (!string.IsNullOrEmpty(PKGID))
            {
                whereSql += $" AND PKGID LIKE '%{PKGID}%'";
            }
            if (!string.IsNullOrEmpty(Location))
            {
                whereSql += $" AND LOCATION LIKE '%{Location}%'";
            }
            if (!string.IsNullOrEmpty(StoreCode))
            {
                whereSql += $" AND STORECODE LIKE '%{StoreCode}%'";
            }
            if (!string.IsNullOrEmpty(PN))
            {
                whereSql += $" AND PARTNO LIKE '%{PN}%'";
            }
            return GetPageAsync("API_TASK_TOTALINFO with(NOLOCK)", "*", whereSql, "Id DESC", pageSize, pageIndex);
        }

        public Task<Tuple<IList<object>, int>> GetLogPageData(string time, int pageSize, int pageIndex)
        {
            string whereSql = "1 = 1";
            if (!string.IsNullOrEmpty(time))
            {
                whereSql += $" AND  DATEDIFF(day,Date,'{time}') = 0";
            }
            return GetPageAsync("Log with(NOLOCK)", "*", whereSql, "Id DESC", pageSize, pageIndex);
        }

        public Task<Tuple<IList<object>, int>> GetApiLogPageData(string time, int pageSize, int pageIndex)
        {
            string whereSql = "1 = 1";
            if (!string.IsNullOrEmpty(time))
            {
                whereSql += $" AND  DATEDIFF(day,CreateDt,'{time}') = 0";
            }
            return GetPageAsync("API_Log with(NOLOCK)", "*", whereSql, "Id DESC", pageSize, pageIndex);
        }

        public Task<IEnumerable<object>> GetSysStatus()
        {
            string sql = @"Select * from ViewBoard_Info with(NOLOCK) WHERE [Group] = '设备状态'";
            return _db.ListAsync<object>(sql);
        }

        public Task<object> GetSystemInfo()
        {
            return _db.GetAsync<object>("Get_SystemInfo", commandType: System.Data.CommandType.StoredProcedure);
        }

        public async Task<bool> UpdateRunStatus(bool status)
        {
            string flag = status ? "ON" : "OFF";
            string sql = $"Update ViewBoard_Info SET Value = '{flag}' WHERE [KEY] = '运行'";
            return await _db.ExecuteAsync(sql) > 0;
        }

        public async Task<Tuple<bool, string>> DelTask(string taskNo)
        {
            bool success = true;
            string msg = "";
            var param = new
            {
                TASKNO = taskNo,
                Cancel = true
            };
            try
            {
                await _db.ExecuteAsync("Exec_切换/作废任务号", param, commandType: System.Data.CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                success = false;
                msg = ex.Message;
            }
            return new Tuple<bool, string>(success, msg);
        }

        public async Task<Tuple<bool, string>> DelWarehouse(string serialNo)
        {
            bool success = true;
            string msg = "";
            var param = new
            {
                SerialNo = serialNo
            };
            try
            {
                await _db.ExecuteAsync("Exec_手工取出料盘", param, commandType: System.Data.CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                success = false;
                msg = ex.Message;
            }
            return new Tuple<bool, string>(success, msg);
        }

        public Task<IEnumerable<TaskModel>> GetTaskList(string ids)
        {
            string sql = $"select TASKNO,PKGID from API_TASK_TOTALINFO where Id In ({ids})";
            return _db.ListAsync<TaskModel>(sql);
        }

        public async Task<Tuple<bool, string>> CompleteTask(string taskNo, string pkgId)
        {
            bool success = true;
            string msg = "";
            var param = new
            {
                TASKNO = taskNo,
                PKGID = pkgId
            };
            try
            {
                await _db.ExecuteAsync("Exec_出库完成_手工", param, commandType: System.Data.CommandType.StoredProcedure);
            }
            catch (Exception ex)
            {
                success = false;
                msg = ex.Message;
            }
            return new Tuple<bool, string>(success, msg);
        }

        public Task<IEnumerable<object>> GetWarehouseWarnList()
        {
            string sql = @"SELECT w.PartNo,MIN(w.MinNum) MinNum,COUNT(1) Num FROM dbo.WarehouseWarn w with(NOLOCK)
                        JOIN dbo.Warehouse h with(NOLOCK) ON w.PartNo = h.CustomerPartNo 
                        GROUP BY w.PartNo
                        HAVING COUNT(1) < MIN(w.MinNum)";
            return _db.ListAsync<object>(sql);
        }

        public Task<IEnumerable<TotalModel>> GetInWarehouse4WMSTotal()
        {
            string sql = @"SELECT SUBSTRING(CONVERT(VARCHAR(100), CreateTime, 21),12,2) AS Hour, COUNT(1) AS COUNT
                        FROM dbo.InWarehouse4WMS with(NOLOCK)
                        WHERE DateDiff(hour,CreateTime,GETDATE()) < 12 AND Qty > 0
                        GROUP BY SUBSTRING(CONVERT(VARCHAR(100), CreateTime, 21),12,2)
                        ORDER BY  SUBSTRING(CONVERT(VARCHAR(100), CreateTime, 21),12,2) desc";
            return _db.ListAsync<TotalModel>(sql);
        }

        public Task<IEnumerable<TotalModel>> GetAPI_TASK_FINISHTotal()
        {
            string sql = @"SELECT SUBSTRING(CONVERT(VARCHAR(100), CreateDt, 21),12,2) AS Hour, COUNT(1) AS COUNT
                        FROM dbo.API_TASK_FINISH with(NOLOCK)
                        WHERE DateDiff(hour,CreateDt,GETDATE()) < 12 AND Qty > 0
                        GROUP BY SUBSTRING(CONVERT(VARCHAR(100), CreateDt, 21),12,2)
                        ORDER BY  SUBSTRING(CONVERT(VARCHAR(100), CreateDt, 21),12,2) desc";
            return _db.ListAsync<TotalModel>(sql);
        }

        public Task<IEnumerable<object>> GetAllTask()
        {
            string sql = @"SELECT ROW_NUMBER() OVER(ORDER BY Id DESC) AS RowNum,*
                          FROM  dbo.API_TASK_TOTALINFO with(NOLOCK)
                          WHERE 1 = 1
                          ORDER BY Id Desc";
            return _db.ListAsync<object>(sql);
        }
    }
}
